﻿using System;
using System.Data;
using MySql.Data.MySqlClient;
using System.Collections.Generic;
using StockTrader.DAL.IDAL;
using StockTrader.DAL.MySQLHelper;
using StockTrader.BusinessService.DataContract;
using StockTrader.BusinessService.DataContract.Structure;

namespace StockTrader.DAL.MySQL
{
    public class HoseOrders : IHoseOrders
    {
        private MySqlConnection _internalConnection;
        private MySqlTransaction _internalADOTransaction = null;

        public void BeginADOTransaction()
        {
            if (_internalConnection.State != System.Data.ConnectionState.Open)
                _internalConnection.Open();
            _internalADOTransaction = _internalConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
        }
        public void RollBackTransaction()
        {
            _internalADOTransaction.Rollback();
            _internalADOTransaction = null;
        }
        public void CommitADOTransaction()
        {
            _internalADOTransaction.Commit();
            _internalADOTransaction = null;
        }
        public void Open(string connString)
        {
            if (_internalConnection == null)
                _internalConnection = new MySqlConnection(connString);
            if (_internalConnection.State != ConnectionState.Open)
                _internalConnection.Open();
        }
        public void Close()
        {
            if (_internalConnection != null && _internalConnection.State != System.Data.ConnectionState.Closed)
                _internalConnection.Close();
        }


        #region SQLstring
        private static string Insert_OrderCancellation = "INSERT INTO 1C_OrderCancellation (TimeStamp, MessageType, Firm, OrderNumber, OrderEntryDate, Offset) VALUES (@TimeStamp, @MessageType, @Firm, @OrderNumber, @OrderEntryDate, @Offset) ON DUPLICATE KEY UPDATE TimeStamp=@TimeStamp, MessageType=@MessageType, Firm=@Firm, OrderNumber=@OrderNumber, OrderEntryDate=@OrderEntryDate, Offset=@Offset;";

        private static string Insert_OrderChange = "INSERT INTO 1D_OrderChange (Timestamp, MessageType, Firm, OrderNumber, OrderEntryDate, ClientID, Filter, Offset) VALUES (@Timestamp, @MessageType, @Firm, @OrderNumber, @OrderEntryDate, @ClientID, @Filter, @Offset) ON DUPLICATE KEY UPDATE Timestamp=@Timestamp, MessageType=@MessageType, Firm=@Firm, OrderNumber=@OrderNumber, OrderEntryDate=@OrderEntryDate, ClientID=@ClientID, Filter=@Filter, Offset=@Offset;";

        private static string Insert_OneFirmPutThroughDeal = "INSERT INTO 1f_onefirmputthroughdeal(TimeStamp, MessageType, Firm, TradeID, ClientIDBuyer, ClientIDSeller, SecuritySymbol, Price, Board, DealID, Filter1, BrokerPortfolioVolBuyer,BrokerClientVolBuyer,MutualFundVolBuyer ,BrokerForeignVolBuyer,  Filter2 ,        BrokerPortfolioVolSeller, BrokerClientVolSeller, MutualFundVolSeller, BrokerForeignVolSeller, Filter3, Offset) VALUES (@TimeStamp, @MessageType, @Firm, @TradeID, @ClientIDBuyer, @ClientIDSeller, @SecuritySymbol, @Price,     @Board, @DealID, @Filter1, @BrokerPortfolioVolBuyer, @BrokerClientVolBuyer, @MutualFundVolBuyer ,                @BrokerForeignVolBuyer,   @Filter2,  @BrokerPortfolioVolSeller, @BrokerClientVolSeller, @MutualFundVolSeller,    @BrokerForeignVolSeller, @Filter3, @Offset) ON DUPLICATE KEY UPDATE  TimeStamp=@TimeStamp, MessageType=@MessageType, Firm=@Firm, TradeID=@TradeID, ClientIDBuyer=@ClientIDBuyer, ClientIDSeller=@ClientIDSeller,  SecuritySymbol=@SecuritySymbol, Price=@Price, Board=@Board, DealID=@DealID, Filter1=@Filter1,  BrokerPortfolioVolBuyer=@BrokerPortfolioVolBuyer, BrokerClientVolBuyer=@BrokerClientVolBuyer,  MutualFundVolBuyer=@MutualFundVolBuyer ,BrokerForeignVolBuyer=@BrokerForeignVolBuyer,   Filter2=@Filter2,                BrokerPortfolioVolSeller=@BrokerPortfolioVolSeller, BrokerClientVolSeller=@BrokerClientVolSeller, MutualFundVolSeller=@MutualFundVolSeller, BrokerForeignVolSeller=@BrokerForeignVolSeller, Filter2=@Filter3, Offset=@Offset;";

        private static string Insert_TwoFirmPutThroughDeal = "INSERT INTO 1G_TwoFirmPutThroughDeal (TimeStamp, MessageType, FirmSeller, TradeIDSeller, ClientIDSeller, ContraFirmBuyer, TradeIDBuyer, SecuritySymbol, Price, Board, DealID, Filter1, BrokerPortfolioVolSeller, BrokerClientVolSeller, MutualFundVolSeller, BrokerForeignVolSeller, Filter2, Offset) VALUES (@TimeStamp, @MessageType, @FirmSeller, @TradeIDSeller, @ClientIDSeller, @ContraFirmBuyer, @TradeIDBuyer, @SecuritySymbol, @Price, @Board, @DealID, @Filter1, @BrokerPortfolioVolSeller, @BrokerClientVolSeller, @MutualFundVolSeller, @BrokerForeignVolSeller, @Filter2, @Offset) ON DUPLICATE KEY UPDATE TimeStamp=@TimeStamp, MessageType=@MessageType, FirmSeller=@FirmSeller, TradeIDSeller=@TradeIDSeller, ClientIDSeller=@ClientIDSeller, ContraFirmBuyer=@ContraFirmBuyer, TradeIDBuyer=@TradeIDBuyer, SecuritySymbol=@SecuritySymbol, Price=@Price, Board=@Board, DealID=@DealID, Filter1=@Filter1, BrokerPortfolioVolSeller=@BrokerPortfolioVolSeller, BrokerClientVolSeller=@BrokerClientVolSeller, MutualFundVolSeller=@MutualFundVolSeller, BrokerForeignVolSeller=@BrokerForeignVolSeller, Filter2=@Filter2, Offset=@Offset;";

        private static string Insert_Advertisement = "INSERT INTO 1E_Advertisement (TimeStamp, MessageType, Firm, TradeID, SecuritySymbol, Side, Volume, Price, Board, Time, AddCancelFlag, Contact, Offset) VALUES (@TimeStamp, @MessageType, @Firm, @TradeID, @SecuritySymbol, @Side, @Volume, @Price, @Board, @Time, @AddCancelFlag, @Contact, @Offset) ON DUPLICATE KEY UPDATE TimeStamp=@TimeStamp, MessageType=@MessageType, Firm=@Firm, TradeID=@TradeID, SecuritySymbol=@SecuritySymbol, Side=@Side, Volume=@Volume, Price=@Price, Board=@Board, Time=@Time, AddCancelFlag=@AddCancelFlag, Contact=@Contact, Offset=@Offset;";

        private static string Insert_NewConditionedOrder = "INSERT INTO 1I_NewConditionedOrder (TimeStamp, MessageType, Firm, TradeID, OrderNumber, ClientID, SecuritySymbol, Side, Volume, PublishedVolume, Price, Board, Filter, PortClientFlag, Filter2, Offset) VALUES (@TimeStamp, @MessageType, @Firm, @TradeID, @OrderNumber, @ClientID, @SecuritySymbol, @Side, @Volume, @PublishedVolume, @Price, @Board, @Filter, @PortClientFlag, @Filter2, @Offset) ON DUPLICATE KEY UPDATE TimeStamp=@TimeStamp, MessageType=@MessageType, Firm=@Firm, TradeID=@TradeID, OrderNumber=@OrderNumber, ClientID=@ClientID, SecuritySymbol=@SecuritySymbol, Side=@Side, Volume=@Volume, PublishedVolume=@PublishedVolume, Price=@Price, Board=@Board, Filter=@Filter, PortClientFlag=@PortClientFlag, Filter2=@Filter2, Offset=@Offset;";

        private static string Insert_OrderConfirmation = "INSERT INTO 2B_OrderConfirmation (Timestamp, MessageType, Firm, OrderNumber, OrderEntryDate, Offset) VALUES (@Timestamp, @MessageType, @Firm, @OrderNumber, @OrderEntryDate, @Offset) ON DUPLICATE KEY UPDATE Timestamp=@Timestamp, MessageType=@MessageType, Firm=@Firm, OrderNumber=@OrderNumber, OrderEntryDate=@OrderEntryDate, Offset=@Offset;";

        private static string Insert_ConfirmOrderCancel = "INSERT INTO 2C_ConfirmOfOrderCancel (Timestamp, MessageType, Firm, CancelShares, OrderNumber, OrderEntryDate, OrderCancelStatus, Offset) VALUES (@Timestamp, @MessageType, @Firm, @CancelShares, @OrderNumber, @OrderEntryDate, @OrderCancelStatus, @Offset) ON DUPLICATE KEY UPDATE Timestamp=@Timestamp, MessageType=@MessageType, Firm=@Firm, CancelShares=@CancelShares, OrderNumber=@OrderNumber, OrderEntryDate=@OrderEntryDate, OrderCancelStatus=@OrderCancelStatus, Offset=@Offset;";

        private static string Insert_ConfirmOrderChange = "INSERT INTO 2D_ConfirmOfOrderChange (Timestamp, MessageType, Firm, OrderNumber, OrderEntryDate, ClientID, PortClientFlag, PublishedVolume, Price, Filter, Offset) VALUES (@Timestamp, @MessageType, @Firm, @OrderNumber, @OrderEntryDate, @ClientID, @PortClientFlag, @PublishedVolume, @Price, @Filter, @Offset) ON DUPLICATE KEY UPDATE Timestamp=@Timestamp, MessageType=@MessageType, Firm=@Firm, OrderNumber=@OrderNumber, OrderEntryDate=@OrderEntryDate, ClientID=@ClientID, PortClientFlag=@PortClientFlag, PublishedVolume=@PublishedVolume, Price=@Price, Filter=@Filter, Offset=@Offset;";

        private static string Insert_DealConfirmation = "INSERT INTO 2E_DealConfirmation (TimeStamp, MessageType, Firm, Side, OrderNumber, OrderEntryDate, Filter, Volume, Price, ConfirmNumber, Offset) VALUES (@TimeStamp, @MessageType, @Firm, @Side, @OrderNumber, @OrderEntryDate, @Filter, @Volume, @Price, @ConfirmNumber, @Offset) ON DUPLICATE KEY UPDATE TimeStamp=@TimeStamp, MessageType=@MessageType, Firm=@Firm, Side=@Side, OrderNumber=@OrderNumber, OrderEntryDate=@OrderEntryDate, Filter=@Filter, Volume=@Volume, Price=@Price, ConfirmNumber=@ConfirmNumber, Offset=@Offset;";

        private static string Insert_PutThroughAcknowledgement = "INSERT INTO 2F_PutThroughAcknowledgment (TimeStamp, MessageType, FirmBuyer, TradeIDBuyer, Side, ContraFirmSeller, TradeIDSeller, SecuritySymbol, Volume, Price, Board, ConfirmNumber, Offset) VALUES (@TimeStamp, @MessageType, @FirmBuyer, @TradeIDBuyer, @Side, @ContraFirmSeller, @TradeIDSeller, @SecuritySymbol, @Volume, @Price, @Board, @ConfirmNumber, @Offset) ON DUPLICATE KEY UPDATE TimeStamp=@TimeStamp, MessageType=@MessageType, FirmBuyer=@FirmBuyer, TradeIDBuyer=@TradeIDBuyer, Side=@Side, ContraFirmSeller=@ContraFirmSeller, TradeIDSeller=@TradeIDSeller, SecuritySymbol=@SecuritySymbol, Volume=@Volume, Price=@Price, Board=@Board, ConfirmNumber=@ConfirmNumber, Offset=@Offset;";

        private static string Insert_CrossDealConfirm = "INSERT INTO 2I_CrossingDealConfirm (Timestamp, Firm, MessageType, OrderNumberBuyer, OrderEntryDateBuyer, OrderNumberSeller, OrderEntryDateSeller, Volume, Price, ConfirmNumber, Offset) VALUES (@Timestamp, @Firm, @MessageType, @OrderNumberBuyer, @OrderEntryDateBuyer, @OrderNumberSeller, @OrderEntryDateSeller, @Volume, @Price, @ConfirmNumber, @Offset) ON DUPLICATE KEY UPDATE Timestamp=@Timestamp, Firm=@Firm, MessageType=@MessageType, OrderNumberBuyer=@OrderNumberBuyer, OrderEntryDateBuyer=@OrderEntryDateBuyer, OrderNumberSeller=@OrderNumberSeller, OrderEntryDateSeller=@OrderEntryDateSeller, Volume=@Volume, Price=@Price, ConfirmNumber=@ConfirmNumber, Offset=@Offset;";

        private static string Insert_PutThroughDealConfirmation = "INSERT INTO 2L_PutThroughDealConfirmation (Timestamp, MessageType, Firm, Side, DealID, ContraFirm, Volume, Price, ConfirmNumber, Offset) VALUES (@Timestamp, @MessageType, @Firm, @Side, @DealID, @ContraFirm, @Volume, @Price, @ConfirmNumber, @Offset) ON DUPLICATE KEY UPDATE Timestamp=@Timestamp, MessageType=@MessageType, Firm=@Firm, Side=@Side, DealID=@DealID, ContraFirm=@ContraFirm, Volume=@Volume, Price=@Price, ConfirmNumber=@ConfirmNumber, Offset=@Offset;";

        private static string Insert_PutThroughDealReply = "INSERT INTO 3B_PutThroughDealReply (TimeStamp, MessageType, Firm, ConfirmNumber, DealID, ClientIDBuyer, ReplyCode, Filter, BrokerPortfolioVolBuyer, BrokerClientVolBuyer, MutualFundVolBuyer, BrokerForeignVolBuyer, Filter2, Offset) VALUES (@TimeStamp, @MessageType, @Firm, @ConfirmNumber, @DealID, @ClientIDBuyer, @ReplyCode, @Filter, @BrokerPortfolioVolBuyer, @BrokerClientVolBuyer, @MutualFundVolBuyer, @BrokerForeignVolBuyer, @Filter2, @Offset) ON DUPLICATE KEY UPDATE TimeStamp=@TimeStamp, MessageType=@MessageType, Firm=@Firm, ConfirmNumber=@ConfirmNumber, DealID=@DealID, ClientIDBuyer=@ClientIDBuyer, ReplyCode=@ReplyCode, Filter=@Filter, BrokerPortfolioVolBuyer=@BrokerPortfolioVolBuyer, BrokerClientVolBuyer=@BrokerClientVolBuyer, MutualFundVolBuyer=@MutualFundVolBuyer, BrokerForeignVolBuyer=@BrokerForeignVolBuyer, Filter2=@Filter2, Offset=@Offset;";

        private static string Insert_Reject = "INSERT INTO 2G_Reject (Timestamp, MessageType, Firm, RejectReasonCode, MessageText, Offset) VALUES (@Timestamp, @MessageType, @Firm, @RejectReasonCode, @MessageText, @Offset) ON DUPLICATE KEY UPDATE Timestamp=@Timestamp, MessageType=@MessageType, Firm=@Firm, RejectReasonCode=@RejectReasonCode, MessageText=@MessageText, Offset=@Offset;";

        private static string Insert_DealPutThroughCancelRequest = "INSERT INTO 3C_DealPutThroughCancelRequest (TimeStamp, MessageType, Firm, ContraFirm, TradeID, ConfirmNumber, SecuritySymbol, Side, Offset) VALUES (@TimeStamp, @MessageType, @Firm, @ContraFirm, @TradeID, @ConfirmNumber, @SecuritySymbol, @Side, @Offset) ON DUPLICATE KEY UPDATE TimeStamp=@TimeStamp, MessageType=@MessageType, Firm=@Firm, ContraFirm=@ContraFirm, TradeID=@TradeID, ConfirmNumber=@ConfirmNumber, SecuritySymbol=@SecuritySymbol, Side=@Side, Offset=@Offset;";

        private static string Insert_DealCancelReply = "INSERT INTO 3D_DealCancelReply (TimeStamp, MessageType, Firm, ConfirmNumber, ReplyCode, Offset) VALUES (@TimeStamp, @MessageType, @Firm, @ConfirmNumber, @ReplyCode, @Offset) ON DUPLICATE KEY UPDATE TimeStamp=@TimeStamp, MessageType=@MessageType, Firm=@Firm, ConfirmNumber=@ConfirmNumber, ReplyCode=@ReplyCode, Offset=@Offset;";

        private static string Select_OrderNew = "SELECT * FROM 1i_newconditionedorder";
        private static string Select_OrderChange = "SELECT * FROM 1d_orderchange";
        private static string Select_OrderCancel = "SELECT * FROM 1c_ordercancellation";
        private static string Select_OFPTDealData = "SELECT * FROM 1f_onefirmputthroughdeal";
        private static string Select_TFPTDealData = "SELECT * FROM 1g_twofirmputthroughdeal";

        private static string Select_ConfirmChange = "SELECT * FROM 2d_confirmoforderchange";
        private static string Select_ConfirmCross = "SELECT * FROM 2i_crossingdealconfirm";
        private static string Select_ConfirmDeal = "SELECT * FROM 2e_dealconfirmation";
        private static string Select_ConfirmOrderNew = "SELECT * FROM 2b_orderconfirmation";
        private static string Select_ConfirmOrderCancel = "SELECT * FROM 2c_confirmofordercancel";
        private static string Select_ConfirmPtDeal = "SELECT * FROM 2l_putthroughdealconfirmation";
        private static string Select_OrderPtCancelRequest = "SELECT * FROM 3c_dealputthroughcancelrequest";
        private static string Select_ConfirmPtDealCancelData = "SELECT * FROM 3d_dealcancelreply";
        private static string Select_PtDealReplyData = "SELECT * FROM 3b_putthroughdealreply";
        private static string Select_getPtACKData = "SELECT * FROM 2f_putthroughacknowledgment";

        private static string Delete_Orders = "delete from 1c_ordercancellation;" +
                                                "delete from 1d_orderchange;" +
                                                "delete from 1e_advertisement;" +
                                                "delete from 1f_onefirmputthroughdeal;" +
                                                "delete from 1g_twofirmputthroughdeal;" +
                                                "delete from 1i_newconditionedorder;" +
                                                "delete from 2b_orderconfirmation;" +
                                                "delete from 2c_confirmofordercancel;" +
                                                "delete from 2d_confirmoforderchange;" +
                                                "delete from 2e_dealconfirmation;" +
                                                "delete from 2f_putthroughacknowledgment;" +
                                                "delete from 2i_crossingdealconfirm;" +
                                                "delete from 2l_putthroughdealconfirmation;" +
                                                "delete from 3b_putthroughdealreply;" +
                                                "delete from 3d_dealcancelreply;";


        private static string DeleteBackupOrders =
            "delete from backup1c_ordercancellation where datediff(transdate,current_date)=0;" +
            "delete from backup1d_orderchange where datediff(transdate,current_date)=0;" +
            "delete from backup1e_advertisement where datediff(transdate,current_date)=0;" +
            "delete from backup1f_onefirmputthroughdeal where datediff(transdate,current_date)=0;" +
            "delete from backup1g_twofirmputthroughdeal where datediff(transdate,current_date)=0;" +
            "delete from backup1i_newconditionedorder where datediff(transdate,current_date)=0;" +
            "delete from backup2b_orderconfirmation where datediff(transdate,current_date)=0;" +
            "delete from backup2c_confirmofordercancel where datediff(transdate,current_date)=0;" +
            "delete from backup2d_confirmoforderchange where datediff(transdate,current_date)=0;" +
            "delete from backup2e_dealconfirmation where datediff(transdate,current_date)=0;" +
            "delete from backup2f_putthroughacknowledgment where datediff(transdate,current_date)=0;" +
            "delete from backup2i_crossingdealconfirm where datediff(transdate,current_date)=0;" +
            "delete from backup2l_putthroughdealconfirmation where datediff(transdate,current_date)=0;" +
            "delete from backup3b_putthroughdealreply where datediff(transdate,current_date)=0;" +
            "delete from backup3d_dealcancelreply where datediff(transdate,current_date)=0;";

        private static string Count_BackupOrders = "count(*) from backup1i_newconditionedorder where datediff(transdate,current_date)=0;";

        private static string InsertBackupHoseOrders = "insert into backup1c_ordercancellation(timestamp, messagetype, firm, ordernumber, orderentrydate, offset) select timestamp, messagetype, firm, ordernumber, orderentrydate, offset from 1c_ordercancellation;" +
                             "insert into backup1d_orderchange(timestamp, messagetype, firm, ordernumber, orderentrydate, clientid, filter, offset) select timestamp, messagetype, firm, ordernumber, orderentrydate, clientid, filter, offset from 1d_orderchange;" +
                             "insert into backup1e_advertisement(timestamp, messagetype, firm, tradeid, securitysymbol, side, volume, price, board, time, addcancelflag, contact, offset) select timestamp, messagetype, firm, tradeid, securitysymbol, side, volume, price, board, time, addcancelflag, contact, offset from 1e_advertisement;" +
                             "insert into backup1f_onefirmputthroughdeal(timestamp, messagetype, firm, tradeid, ClientIDBuyer, ClientIDSeller, SecuritySymbol, price, board, DealID, Filter1, BrokerPortfolioVolBuyer, BrokerClientVolBuyer, MutualFundVolBuyer, BrokerForeignVolBuyer, Filter2,BrokerPortfolioVolSeller, BrokerClientVolSeller, MutualFundVolSeller,BrokerForeignVolSeller,Filter3,Offset ) select timestamp, messagetype, firm, tradeid, ClientIDBuyer, ClientIDSeller, SecuritySymbol, price, board, DealID, Filter1, BrokerPortfolioVolBuyer, BrokerClientVolBuyer, MutualFundVolBuyer, BrokerForeignVolBuyer, Filter2,BrokerPortfolioVolSeller, BrokerClientVolSeller, MutualFundVolSeller,BrokerForeignVolSeller,Filter3,Offset  from 1f_onefirmputthroughdeal;" +
                             "insert into backup1g_twofirmputthroughdeal(timestamp, messagetype, FirmSeller, TradeIDSeller, ClientIDSeller, ContraFirmBuyer, TradeIDBuyer,SecuritySymbol, price, board, DealID, Filter1, BrokerPortfolioVolSeller, BrokerClientVolSeller, MutualFundVolSeller, BrokerForeignVolSeller, Filter2, Offset) select timestamp, messagetype, FirmSeller, TradeIDSeller, ClientIDSeller, ContraFirmBuyer, TradeIDBuyer,SecuritySymbol, price, board, DealID, Filter1, BrokerPortfolioVolSeller, BrokerClientVolSeller, MutualFundVolSeller, BrokerForeignVolSeller, Filter2, Offset from 1g_twofirmputthroughdeal;" +
                             "insert into backup1i_newconditionedorder(timestamp, messagetype, Firm, TradeID, OrderNumber, ClientID, SecuritySymbol, Side, Volume, PublishedVolume, Price, Board, Filter, PortClientFlag, Filter2, Offset) select timestamp, messagetype, Firm, TradeID, OrderNumber, ClientID, SecuritySymbol, Side, Volume, PublishedVolume, Price, Board, Filter, PortClientFlag, Filter2, Offset from 1i_newconditionedorder;" +
                             "insert into backup2c_confirmofordercancel(timestamp, messagetype, Firm, CancelShares, OrderNumber, OrderEntryDate, OrderCancelStatus, Offset) select timestamp, messagetype, Firm, CancelShares, OrderNumber, OrderEntryDate, OrderCancelStatus, Offset from 2c_confirmofordercancel;" +
                             "insert into backup2b_orderconfirmation(timestamp, messagetype, Firm, OrderNumber, OrderEntryDate, Offset) select timestamp, messagetype, Firm, OrderNumber, OrderEntryDate, Offset from 2b_orderconfirmation;" +
                             "insert into backup2d_confirmoforderchange(timestamp, messagetype, Firm, OrderNumber, OrderEntryDate,ClientID,PortClientFlag, PublishedVolume,Price,Filter, Offset) select timestamp, messagetype, Firm, OrderNumber, OrderEntryDate,ClientID,PortClientFlag, PublishedVolume,Price,Filter, Offset from 2d_confirmoforderchange;" +
                             "insert into backup2e_dealconfirmation(timestamp, messagetype, Firm, Side, OrderNumber, OrderEntryDate, Filter, Volume,Price, ConfirmNumber, Offset) select timestamp, messagetype, Firm, Side, OrderNumber, OrderEntryDate, Filter, Volume,Price, ConfirmNumber, Offset from 2e_dealconfirmation;" +
                             "insert into backup2f_putthroughacknowledgment(timestamp, messagetype, FirmBuyer, TradeIDBuyer, Side, ContraFirmSeller, TradeIDSeller, SecuritySymbol, Volume,Price,Board, ConfirmNumber, Offset) select timestamp, messagetype, FirmBuyer, TradeIDBuyer, Side, ContraFirmSeller, TradeIDSeller, SecuritySymbol, Volume,Price,Board, ConfirmNumber, Offset from 2f_putthroughacknowledgment;" +
                             "insert into backup2i_crossingdealconfirm(timestamp, Firm, messagetype, OrderNumberBuyer, OrderEntryDateBuyer, OrderNumberSeller, OrderEntryDateSeller, Volume, Price, ConfirmNumber, Offset) select timestamp, Firm, messagetype, OrderNumberBuyer, OrderEntryDateBuyer, OrderNumberSeller, OrderEntryDateSeller, Volume, Price, ConfirmNumber, Offset from 2i_crossingdealconfirm;" +
                             "insert into backup2l_putthroughdealconfirmation(timestamp,  messagetype,Firm, Side, DealID, ContraFirm, Volume, Price, ConfirmNumber, Offset) select timestamp,  messagetype,Firm, Side, DealID, ContraFirm, Volume, Price, ConfirmNumber, Offset from 2l_putthroughdealconfirmation;" +
                             "insert into backup3b_putthroughdealreply(timestamp,  messagetype,Firm, ConfirmNumber, DealID, ClientIDBuyer, ReplyCode, Filter, BrokerPortfolioVolBuyer,BrokerClientVolBuyer,MutualFundVolBuyer,BrokerForeignVolBuyer,Filter2, Offset) select timestamp,  messagetype,Firm, ConfirmNumber, DealID, ClientIDBuyer, ReplyCode, Filter, BrokerPortfolioVolBuyer,BrokerClientVolBuyer,MutualFundVolBuyer,BrokerForeignVolBuyer,Filter2, Offset from 3b_putthroughdealreply;" +
                             "insert into backup3d_dealcancelreply(timestamp,  messagetype,Firm, ConfirmNumber, ReplyCode, Offset) select timestamp,  messagetype,Firm, ConfirmNumber, ReplyCode, Offset from 3d_dealcancelreply;";

        private static string Insert_SecuritiesInfo = "INSERT INTO _SECURITIES_INFO(STOCKNO, SYMBOL, MARKET) VALUE (@STOCKNO, @STOCKSYMBOL, @MARKET) ON DUPLICATE KEY UPDATE STOCKNO=@STOCKNO, SYMBOL=@STOCKSYMBOL, MARKET=@MARKET";


        private static string Select_Backup_OrderNew = "SELECT * FROM backup1i_newconditionedorder where transdate between @fromdate and @todate";
        private static string Select_Backup_OrderChange = "SELECT * FROM backup1d_orderchange where transdate between @fromdate and @todate";
        private static string Select_Backup_OrderCancel = "SELECT * FROM backup1c_ordercancellation where transdate between @fromdate and @todate";
        private static string Select_Backup_OFPTDealData = "SELECT * FROM backup1f_onefirmputthroughdeal where transdate between @fromdate and @todate";
        private static string Select_Backup_TFPTDealData = "SELECT * FROM backup1g_twofirmputthroughdeal where transdate between @fromdate and @todate";

        private static string Select_Backup_ConfirmChange = "SELECT * FROM backup2d_confirmoforderchange where transdate between @fromdate and @todate";
        private static string Select_Backup_ConfirmCross = "SELECT * FROM backup2i_crossingdealconfirm where transdate between @fromdate and @todate";
        private static string Select_Backup_ConfirmDeal = "SELECT * FROM backup2e_dealconfirmation where transdate between @fromdate and @todate";
        private static string Select_Backup_ConfirmOrderNew = "SELECT * FROM backup2b_orderconfirmation where transdate between @fromdate and @todate";
        private static string Select_Backup_ConfirmOrderCancel = "SELECT * FROM backup2c_confirmofordercancel where transdate between @fromdate and @todate";
        private static string Select_Backup_ConfirmPtDeal = "SELECT * FROM backup2l_putthroughdealconfirmation where transdate between @fromdate and @todate";
        private static string Select_Backup_OrderPtCancelRequest = "SELECT * FROM backup3c_dealputthroughcancelrequest";
        private static string Select_Backup_ConfirmPtDealCancelData = "SELECT * FROM backup3d_dealcancelreply where transdate between @fromdate and @todate";
        private static string Select_Backup_PtDealReplyData = "SELECT * FROM backup3b_putthroughdealreply where transdate between @fromdate and @todate";
        private static string Select_Backup_getPtACKData = "SELECT * FROM backup2f_putthroughacknowledgment where transdate between @fromdate and @todate";

        private static string GET_VIPACCOUNT = "SELECT * FROM VIPACCOUNT";

        #endregion

        private static string PARM_FROMDATE = "@FromDate";
        private static string PARM_TODATE = "@ToDate";

        #region Count Hose order messages

        private static string Count_OrderCancellation = "SELECT COUNT(*) FROM 1C_OrderCancellation";
        private static string Count_OrderChange = "SELECT COUNT(*) FROM 1D_OrderChange";
        private static string Count_Advertisement = "SELECT COUNT(*) FROM 1C_OrderCancellation";

        private static string Count_OneFirmPutThroughDeal = "SELECT COUNT(*) FROM 1F_OneFirmPutThroughDeal";
        private static string Count_TwoFirmPutThroughDeal = "SELECT COUNT(*) FROM 1G_TwoFirmPutThroughDeal";
        private static string Count_NewConditionedOrder = "SELECT COUNT(*) FROM 1I_NewConditionedOrder";
        private static string Count_OrderConfirmation = "SELECT COUNT(*) FROM 2B_OrderConfirmation";
        private static string Count_ConfirmOrderCancel = "SELECT COUNT(*) FROM 2C_ConfirmOfOrderCancel";
        private static string Count_ConfirmOrderChange = "SELECT COUNT(*) FROM 2D_ConfirmOfOrderChange";
        private static string Count_DealConfirmation = "SELECT COUNT(*) FROM 2E_DealConfirmation";
        private static string Count_PutThroughAcknowledgement = "SELECT COUNT(*) FROM 2F_PutThroughAcknowledgment";
        private static string Count_Reject = "SELECT COUNT(*) FROM 2G_Reject";
        private static string Count_CrossDealConfirm = "SELECT COUNT(*) FROM 2I_CrossingDealConfirm";
        private static string Count_PutThroughDealConfirmation = "SELECT COUNT(*) FROM 2L_PutThroughDealConfirmation";

        private static string Count_PutThroughDealReply = "SELECT COUNT(*) FROM 3B_PutThroughDealReply";
        private static string Count_DealPutThroughCancelRequest = "SELECT COUNT(*) FROM 3C_DealPutThroughCancelRequest";
        private static string Count_DealCancelReply = "SELECT COUNT(*) FROM 3D_DealCancelReply";
        #endregion

        #region Delete Hose order messages

        private static string Delete_OrderCancellation = "DELETE FROM 1C_OrderCancellation";
        private static string Delete_OrderChange = "DELETE FROM 1D_OrderChange";
        private static string Delete_Advertisement = "DELETE FROM 1e_advertisement";

        private static string Delete_OneFirmPutThroughDeal = "DELETE FROM 1F_OneFirmPutThroughDeal";
        private static string Delete_TwoFirmPutThroughDeal = "DELETE FROM 1G_TwoFirmPutThroughDeal";
        private static string Delete_NewConditionedOrder = "DELETE FROM 1I_NewConditionedOrder";
        private static string Delete_OrderConfirmation = "DELETE FROM 2B_OrderConfirmation";
        private static string Delete_ConfirmOrderCancel = "DELETE FROM 2C_ConfirmOfOrderCancel";
        private static string Delete_ConfirmOrderChange = "DELETE FROM 2D_ConfirmOfOrderChange";
        private static string Delete_DealConfirmation = "DELETE FROM 2E_DealConfirmation";
        private static string Delete_PutThroughAcknowledgement = "DELETE FROM 2F_PutThroughAcknowledgment";
        private static string Delete_Reject = "DELETE FROM 2G_Reject";
        private static string Delete_CrossDealConfirm = "DELETE FROM 2I_CrossingDealConfirm";
        private static string Delete_PutThroughDealConfirmation = "DELETE FROM 2L_PutThroughDealConfirmation";

        private static string Delete_PutThroughDealReply = "DELETE FROM 3B_PutThroughDealReply";
        private static string Delete_DealPutThroughCancelRequest = "DELETE FROM 3C_DealPutThroughCancelRequest";
        private static string Delete_DealCancelReply = "DELETE FROM 3D_DealCancelReply";
        #endregion

        #region Parameters
        private const string PARM_TRANSDATE = "@TRANSDATE";
        private const string PARM_REPLYCODE = "@REPLYCODE";
        private const string PARM_TIMESTAMP = "@TIMESTAMP";
        private const string PARM_MESSAGETYPE = "@MESSAGETYPE";
        private const string PARM_FIRM = "@FIRM";
        private const string PARM_TRADEID = "@TRADEID";
        private const string PARM_ORDERNUMBER = "@ORDERNUMBER";
        private const string PARM_CLIENTID = "@CLIENTID";
        private const string PARM_VOLUME = "@VOLUME";
        private const string PARM_PUBLISHEDVOLUME = "@PUBLISHEDVOLUME";
        private const string PARM_BOARD = "@BOARD";
        private const string PARM_DEALID = "@DEALID";
        private const string PARM_FILTER = "@FILTER";
        private const string PARM_FILTER1 = "@FILTER1";
        private const string PARM_FILTER2 = "@FILTER2";
        private const string PARM_FILTER3 = "@FILTER3";
        private const string PARM_PORTCLIENTFLAG = "@PORTCLIENTFLAG";
        private const string PARM_ORDERENTRYDATE = "@ORDERENTRYDATE";
        private const string PARM_OFFSET = "@OFFSET";
        private const string PARM_SIDE = "@SIDE";
        private const string PARM_ADDCANCELFLAG = "@ADDCANCELFLAG";
        private const string PARM_CONTACT = "@CONTACT";
        private const string PARM_TIME = "@TIME";
        private const string PARM_CANCELSHARES = "@CANCELSHARES";
        private const string PARM_ORDERCANCELSTATUS = "@ORDERCANCELSTATUS";
        private const string PARM_CLIENTIDBUYER = "@CLIENTIDBUYER";
        private const string PARM_CLIENTIDSELLER = "@CLIENTIDSELLER";
        private const string PARM_BROKERPORTFOLIOVOLBUYER = "@BROKERPORTFOLIOVOLBUYER";
        private const string PARM_BROKERCLIENTVOLBUYER = "@BROKERCLIENTVOLBUYER";
        private const string PARM_MUTUALFUNDVOLBUYER = "@MUTUALFUNDVOLBUYER";
        private const string PARM_BROKERFOREIGNVOLBUYER = "@BROKERFOREIGNVOLBUYER";
        private const string PARM_BROKERPORTFOLIOVOLSELLER = "@BROKERPORTFOLIOVOLSELLER";
        private const string PARM_BROKERCLIENTVOLSELLER = "@BROKERCLIENTVOLSELLER";
        private const string PARM_MUTUALFUNDVOLSELLER = "@MUTUALFUNDVOLSELLER";
        private const string PARM_BROKERFOREIGNVOLSELLER = "@BROKERFOREIGNVOLSELLER";
        private const string PARM_CONTRAFIRMBUYER = "@CONTRAFIRMBUYER";
        private const string PARM_CONTRAFIRMSELLER = "@CONTRAFIRMSELLER";
        private const string PARM_CONTRAFIRM = "@CONTRAFIRM";
        private const string PARM_TRADEIDBUYER = "@TRADEIDBUYER";
        private const string PARM_TRADEIDSELLER = "@TRADEIDSELLER";
        private const string PARM_CONFIRMNUMBER = "@CONFIRMNUMBER";
        private const string PARM_FIRMBUYER = "@FIRMBUYER";
        private const string PARM_REJECTREASONCODE = "@REJECTREASONCODE";
        private const string PARM_MESSAGETEXT = "@MESSAGETEXT";
        private const string PARM_ORDERNUMBERBUYER = "@ORDERNUMBERBUYER";
        private const string PARM_ORDERNUMBERSELLER = "@ORDERNUMBERSELLER";
        private const string PARM_ORDERENTRYDATEBUYER = "@ORDERENTRYDATEBUYER";
        private const string PARM_ORDERENTRYDATESELLER = "@ORDERENTRYDATESELLER";


        private const string PARM_ORDERNO = "@ORDERNO";
        private const string PARM_NEXTORDERID = "@NEXTORDERID";
        private const string PARM_ACCOUNTID = "@ACCOUNTID";
        private const string PARM_MEMBERID = "@MEMBERID";
        private const string PARM_BROKERID = "@BROKERID";
        private const string PARM_OPENDATE = "@OPENDATE";
        private const string PARM_GTD = "@GTD";
        private const string PARM_COMLETIONDATE = "@COMPLETIONDATE";
        private const string PARM_ORDERTYPE = "@ORDERTYPE";
        private const string PARM_ORDERSIDE = "@ORDERSIDE";
        private const string PARM_ORDERCLASS = "@ORDERCLASS";
        private const string PARM_ORDERMODE = "@ORDERMODE";
        private const string PARM_ORDERSTATUS = "@ORDERSTATUS";
        private const string PARM_PRIORITY = "@PRIORITY";
        private const string PARM_SYMBOL = "@SECURITYSYMBOL";
        private const string PARM_PRICE = "@PRICE";
        private const string PARM_QUANTITY = "@QUANTITY";
        private const string PARM_ORDERFEE = "@ORDERFEE";
        private const string PARM_MATCHQTTY = "@MATCHQTTY";
        private const string PARM_CANCELLQTTY = "@CANCELLQTTY";
        private const string PARM_REMAINQTTY = "@REMAINQTTY";
        private const string PARM_QUOTEPRICE = "@QUOTEPRICE";
        private const string PARM_REJECTREASON = "@REJECTREASON";
        private const string PARM_PHONE = "@PHONE";
        private const string PARM_SESSIONNO = "@SESSIONNO";
        private const string PARM_SETTLMENTDATE = "@SETTLEMENTDATE";
        private const string PARM_SETTMENTTYPE = "@SETTLEMENTTYPE";
        private const string PARM_HOLDINGID = "@HOLDINGID";
        #endregion

        //Functions

        #region Insert IOrder Members


        public void InsertAdvertisement(string timestamp, string messagetype, string firm, string tradeid, string symbol, string side, long volume, string price, string board, string addcancelflag, string contact, int offset)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                                new MySqlParameter(PARM_TIMESTAMP, MySqlDbType.VarChar, 6),
                                                new MySqlParameter(PARM_MESSAGETYPE, MySqlDbType.VarChar, 2),
                                                new MySqlParameter(PARM_FIRM, MySqlDbType.VarChar,3),
                                                new MySqlParameter(PARM_TRADEID, MySqlDbType.VarChar,4),
                                                new MySqlParameter(PARM_SYMBOL, MySqlDbType.VarChar,8),
                                                new MySqlParameter(PARM_SIDE, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_VOLUME, MySqlDbType.Int64),
                                                new MySqlParameter(PARM_PRICE, MySqlDbType.VarChar,12),
                                                new MySqlParameter(PARM_BOARD, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_TIME, MySqlDbType.VarChar,6),
                                                new MySqlParameter(PARM_ADDCANCELFLAG, MySqlDbType.VarChar,8),
                                                new MySqlParameter(PARM_CONTACT, MySqlDbType.VarChar,20),
                                                new MySqlParameter(PARM_OFFSET, MySqlDbType.Int32)};
                parms[0].Value = timestamp;
                parms[1].Value = messagetype;
                parms[2].Value = firm;
                parms[3].Value = tradeid;
                parms[4].Value = symbol;
                parms[5].Value = side;
                parms[6].Value = volume;
                parms[7].Value = price;
                parms[8].Value = board;
                parms[9].Value = timestamp;
                parms[10].Value = addcancelflag;
                parms[11].Value = contact;
                parms[12].Value = offset;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, Insert_Advertisement, parms);
            }
            catch
            {
                throw;
            }
        }

        public void InsertConfirmOrderCancel(string timestamp, string messagetype, string firm, long cancelshare, string ordernumber, string orderEntryDate, string orderCancelStatus, int orderOffset)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                                new MySqlParameter(PARM_TIMESTAMP, MySqlDbType.VarChar, 6),
                                                new MySqlParameter(PARM_MESSAGETYPE, MySqlDbType.VarChar, 2),
                                                new MySqlParameter(PARM_FIRM, MySqlDbType.VarChar,3),
                                                new MySqlParameter(PARM_CANCELSHARES, MySqlDbType.Int64),
                                                new MySqlParameter(PARM_ORDERNUMBER, MySqlDbType.VarChar,8),
                                                new MySqlParameter(PARM_ORDERENTRYDATE, MySqlDbType.VarChar,4),
                                                new MySqlParameter(PARM_ORDERCANCELSTATUS, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_OFFSET, MySqlDbType.Int32)};
                parms[0].Value = timestamp;
                parms[1].Value = messagetype;
                parms[2].Value = firm;
                parms[3].Value = cancelshare;
                parms[4].Value = ordernumber;
                parms[5].Value = orderEntryDate;
                parms[6].Value = orderCancelStatus;
                parms[7].Value = orderOffset;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, Insert_ConfirmOrderCancel, parms);
            }
            catch
            {
                throw;
            }
        }

        public void InsertOrderCancellation(string timestamp, string messagetype, string firm, string ordernumber, string orderentrydate, int offset)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                        new MySqlParameter(PARM_TIMESTAMP, MySqlDbType.VarChar, 6),
                                        new MySqlParameter(PARM_MESSAGETYPE, MySqlDbType.VarChar, 2),
                                        new MySqlParameter(PARM_FIRM, MySqlDbType.VarChar,3),
                                        new MySqlParameter(PARM_ORDERNUMBER, MySqlDbType.VarChar,8),
                                        new MySqlParameter(PARM_ORDERENTRYDATE, MySqlDbType.VarChar,4),
                                        new MySqlParameter(PARM_OFFSET, MySqlDbType.Int32)};
                parms[0].Value = timestamp;
                parms[1].Value = messagetype;
                parms[2].Value = firm;
                parms[3].Value = ordernumber;
                parms[4].Value = orderentrydate;
                parms[5].Value = offset;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, Insert_OrderCancellation, parms);
            }
            catch
            {
                throw;
            }
        }

        public void InsertOrderChange(string timestamp, string messagetype, string firm, string ordernumber, string orderentrydate, string clientid, string filter, int offset)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                                new MySqlParameter(PARM_TIMESTAMP, MySqlDbType.VarChar, 6),
                                                new MySqlParameter(PARM_MESSAGETYPE, MySqlDbType.VarChar, 2),
                                                new MySqlParameter(PARM_FIRM, MySqlDbType.VarChar,3),
                                                new MySqlParameter(PARM_ORDERNUMBER, MySqlDbType.VarChar,8),
                                                new MySqlParameter(PARM_ORDERENTRYDATE, MySqlDbType.VarChar,4),
                                                new MySqlParameter(PARM_CLIENTID, MySqlDbType.VarChar,10),
                                                new MySqlParameter(PARM_FILTER, MySqlDbType.VarChar, 17),
                                                new MySqlParameter(PARM_OFFSET, MySqlDbType.Int32)};
                parms[0].Value = timestamp;
                parms[1].Value = messagetype;
                parms[2].Value = firm;
                parms[3].Value = ordernumber;
                parms[4].Value = orderentrydate;
                parms[5].Value = clientid;
                parms[6].Value = filter;
                parms[7].Value = offset;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, Insert_OrderChange, parms);
            }
            catch
            {
                throw;
            }
        }

        public void InsertOneFirmPutThroughDeal(string timestamp, string messagetype, string firm, string tradeid, string clientIdBuyer, string ClientIdSeller, string symbol, string price, string board, string dealid, string filter1, long brokerPortfolioVolBuyer, long brokerClientVolBuyer, long mutualFundVolBuyer, long brokerForeignVolBuyer, string filter2, long brokerPortfolioVolSeller, long brokerClientVolSeller, long mutualFundVolSeller, long brokerForeignVolSeller, string filter3, int offset)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                                new MySqlParameter(PARM_TIMESTAMP, MySqlDbType.VarChar, 6),
                                                new MySqlParameter(PARM_MESSAGETYPE, MySqlDbType.VarChar, 2),
                                                new MySqlParameter(PARM_FIRM, MySqlDbType.VarChar,3),
                                                new MySqlParameter(PARM_TRADEID, MySqlDbType.VarChar,4),
                                                new MySqlParameter(PARM_CLIENTIDBUYER, MySqlDbType.VarChar,10),
                                                new MySqlParameter(PARM_CLIENTIDSELLER, MySqlDbType.VarChar,10),
                                                new MySqlParameter(PARM_SYMBOL, MySqlDbType.VarChar,8),
                                                new MySqlParameter(PARM_PRICE, MySqlDbType.VarChar, 12),
                                                new MySqlParameter(PARM_BOARD, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_DEALID, MySqlDbType.VarChar,5),
                                                new MySqlParameter(PARM_FILTER1, MySqlDbType.VarChar,8),
                                                new MySqlParameter(PARM_BROKERPORTFOLIOVOLBUYER, MySqlDbType.Int64),
                                                new MySqlParameter(PARM_BROKERCLIENTVOLBUYER, MySqlDbType.Int64),
                                                new MySqlParameter(PARM_MUTUALFUNDVOLBUYER, MySqlDbType.Int64),
                                                new MySqlParameter(PARM_BROKERFOREIGNVOLBUYER, MySqlDbType.Int64),
                                                new MySqlParameter(PARM_FILTER2, MySqlDbType.VarChar,32),
                                                new MySqlParameter(PARM_BROKERPORTFOLIOVOLSELLER, MySqlDbType.Int64),
                                                new MySqlParameter(PARM_BROKERCLIENTVOLSELLER, MySqlDbType.Int64),
                                                new MySqlParameter(PARM_MUTUALFUNDVOLSELLER, MySqlDbType.Int64),
                                                new MySqlParameter(PARM_BROKERFOREIGNVOLSELLER, MySqlDbType.Int64),
                                                new MySqlParameter(PARM_FILTER3, MySqlDbType.VarChar,32),
                                                new MySqlParameter(PARM_OFFSET, MySqlDbType.Int32)};
                parms[0].Value = timestamp;
                parms[1].Value = messagetype;
                parms[2].Value = firm;
                parms[3].Value = tradeid;
                parms[4].Value = clientIdBuyer;
                parms[5].Value = ClientIdSeller;
                parms[6].Value = symbol;
                parms[7].Value = price;
                parms[8].Value = board;
                parms[9].Value = dealid;
                parms[10].Value = filter1;
                parms[11].Value = brokerPortfolioVolBuyer;
                parms[12].Value = brokerClientVolBuyer;
                parms[13].Value = mutualFundVolBuyer;
                parms[14].Value = brokerForeignVolBuyer;
                parms[15].Value = filter2;
                parms[16].Value = brokerPortfolioVolSeller;
                parms[17].Value = brokerClientVolSeller;
                parms[18].Value = mutualFundVolSeller;
                parms[19].Value = brokerForeignVolSeller;
                parms[20].Value = filter3;
                parms[21].Value = offset;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, Insert_OneFirmPutThroughDeal, parms);
            }
            catch
            {
                throw;
            }
        }

        public void InsertTwoFirmPutThroughDeal(string timestamp, string messagetype, string firmSeller, string tradeIDSeller, string ClientIdSeller, string contraFirmBuyer, string tradeIDBuyer, string symbol, string price, string board, string dealid, string filter1, long brokerPortfolioVolSeller, long brokerClientVolSeller, long mutualFundVolSeller, long brokerForeignVolSeller, string filter2, int offset)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                                new MySqlParameter(PARM_TIMESTAMP, MySqlDbType.VarChar, 6),
                                                new MySqlParameter(PARM_MESSAGETYPE, MySqlDbType.VarChar, 2),
                                                new MySqlParameter("@FIRMSELLER", MySqlDbType.VarChar,3),
                                                new MySqlParameter(PARM_TRADEIDSELLER, MySqlDbType.VarChar,4),
                                                new MySqlParameter(PARM_CLIENTIDSELLER, MySqlDbType.VarChar,10),
                                                new MySqlParameter(PARM_CONTRAFIRMBUYER, MySqlDbType.VarChar,2),
                                                
                                                new MySqlParameter(PARM_TRADEIDBUYER, MySqlDbType.VarChar,4),
                                                new MySqlParameter(PARM_SYMBOL, MySqlDbType.VarChar,8),
                                                new MySqlParameter(PARM_PRICE, MySqlDbType.VarChar, 12),
                                                new MySqlParameter(PARM_BOARD, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_DEALID, MySqlDbType.VarChar,5),
                                                new MySqlParameter(PARM_FILTER1, MySqlDbType.VarChar,8),
                                                new MySqlParameter(PARM_BROKERPORTFOLIOVOLSELLER, MySqlDbType.Int64),
                                                new MySqlParameter(PARM_BROKERCLIENTVOLSELLER, MySqlDbType.Int64),
                                                new MySqlParameter(PARM_MUTUALFUNDVOLSELLER, MySqlDbType.Int64),
                                                new MySqlParameter(PARM_BROKERFOREIGNVOLSELLER, MySqlDbType.Int64),
                                                new MySqlParameter(PARM_FILTER2, MySqlDbType.VarChar,32),
                                                new MySqlParameter(PARM_OFFSET, MySqlDbType.Int32)};
                parms[0].Value = timestamp;
                parms[1].Value = messagetype;
                parms[2].Value = firmSeller;
                parms[3].Value = tradeIDSeller;
                parms[4].Value = ClientIdSeller;
                parms[5].Value = contraFirmBuyer;
                parms[6].Value = tradeIDBuyer;
                parms[7].Value = symbol;
                parms[8].Value = price;
                parms[9].Value = board;
                parms[10].Value = dealid;
                parms[11].Value = filter1;
                parms[12].Value = brokerPortfolioVolSeller;
                parms[13].Value = brokerClientVolSeller;
                parms[14].Value = mutualFundVolSeller;
                parms[15].Value = brokerForeignVolSeller;
                parms[16].Value = filter2;
                parms[17].Value = offset;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, Insert_TwoFirmPutThroughDeal, parms);
            }
            catch
            {
                throw;
            }
        }

        public void InsertNewConditionedOrder(string timestamp, string messagetype, string firm, string tradeId, string orderNumber, string clientId, string symbol, string side, long volume, long publishedVolume, string price, string board, string filter, string portClientFlag, string filter2, int offset)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                                new MySqlParameter(PARM_TIMESTAMP, MySqlDbType.VarChar, 6),
                                                new MySqlParameter(PARM_MESSAGETYPE, MySqlDbType.VarChar, 2),
                                                new MySqlParameter(PARM_FIRM, MySqlDbType.VarChar,3),
                                                new MySqlParameter(PARM_TRADEID, MySqlDbType.VarChar,4),
                                                new MySqlParameter(PARM_ORDERNUMBER, MySqlDbType.VarChar,8),
                                                new MySqlParameter(PARM_CLIENTID, MySqlDbType.VarChar,10),
                                                new MySqlParameter(PARM_SYMBOL, MySqlDbType.VarChar,8),
                                                new MySqlParameter(PARM_SIDE, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_VOLUME, MySqlDbType.Int64),
                                                new MySqlParameter(PARM_PUBLISHEDVOLUME, MySqlDbType.Int64),
                                                new MySqlParameter(PARM_PRICE, MySqlDbType.VarChar,12),
                                                new MySqlParameter(PARM_BOARD, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_FILTER, MySqlDbType.VarChar,5),
                                                new MySqlParameter(PARM_PORTCLIENTFLAG, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_FILTER2, MySqlDbType.VarChar,5),
                                                new MySqlParameter(PARM_OFFSET, MySqlDbType.Int32)};
                parms[0].Value = timestamp;
                parms[1].Value = messagetype;
                parms[2].Value = firm;
                parms[3].Value = tradeId;
                parms[4].Value = orderNumber;
                parms[5].Value = clientId;
                parms[6].Value = symbol;
                parms[7].Value = side;
                parms[8].Value = volume;
                parms[9].Value = publishedVolume;
                parms[10].Value = price;
                parms[11].Value = board;
                parms[12].Value = filter;
                parms[13].Value = portClientFlag;
                parms[14].Value = filter2;
                parms[15].Value = offset;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, Insert_NewConditionedOrder, parms);
            }
            catch
            {
                throw;
            }
        }

        public void InsertOrderConfirmation(string timestamp, string messagetype, string firm, string orderNumber, string orderEntryDate, int offset)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                                new MySqlParameter(PARM_TIMESTAMP, MySqlDbType.VarChar, 6),
                                                new MySqlParameter(PARM_MESSAGETYPE, MySqlDbType.VarChar, 2),
                                                new MySqlParameter(PARM_FIRM, MySqlDbType.VarChar,3),
                                                new MySqlParameter(PARM_ORDERNUMBER, MySqlDbType.VarChar,8),
                                                new MySqlParameter(PARM_ORDERENTRYDATE, MySqlDbType.VarChar,4),
                                                new MySqlParameter(PARM_OFFSET, MySqlDbType.Int32)};
                parms[0].Value = timestamp;
                parms[1].Value = messagetype;
                parms[2].Value = firm;
                parms[3].Value = orderNumber;
                parms[4].Value = orderEntryDate;
                parms[5].Value = offset;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, Insert_OrderConfirmation, parms);
            }
            catch
            {
                throw;
            }
        }

        public void InsertConfirmOrderChange(string timestamp, string messagetype, string firm, string orderNumber, string orderEntryDate, string clientId, string portClientFlag, long publishVolume, string price, string filter, int offset)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                                new MySqlParameter(PARM_TIMESTAMP, MySqlDbType.VarChar, 6),
                                                new MySqlParameter(PARM_MESSAGETYPE, MySqlDbType.VarChar, 2),
                                                new MySqlParameter(PARM_FIRM, MySqlDbType.VarChar,3),
                                                new MySqlParameter(PARM_ORDERNUMBER, MySqlDbType.VarChar,8),
                                                new MySqlParameter(PARM_ORDERENTRYDATE, MySqlDbType.VarChar,4),
                                                new MySqlParameter(PARM_CLIENTID, MySqlDbType.VarChar,10),
                                                new MySqlParameter(PARM_PORTCLIENTFLAG, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_PUBLISHEDVOLUME, MySqlDbType.Int64),
                                                new MySqlParameter(PARM_PRICE, MySqlDbType.VarChar,6),
                                                new MySqlParameter(PARM_FILTER, MySqlDbType.VarChar,8),
                                                new MySqlParameter(PARM_OFFSET, MySqlDbType.Int32)};
                parms[0].Value = timestamp;
                parms[1].Value = messagetype;
                parms[2].Value = firm;
                parms[3].Value = orderNumber;
                parms[4].Value = orderEntryDate;
                parms[5].Value = clientId;
                parms[6].Value = portClientFlag;
                parms[7].Value = publishVolume;
                parms[8].Value = price;
                parms[9].Value = filter;
                parms[10].Value = offset;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, Insert_ConfirmOrderChange, parms);
            }
            catch
            {
                throw;
            }
        }

        public void InsertDealConfirmation(string timestamp, string messagetype, string firm, string side, string orderNumber, string orderEntryDate, string filter, long volume, string price, string confirmNumber, int offset)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                                new MySqlParameter(PARM_TIMESTAMP, MySqlDbType.VarChar, 6),
                                                new MySqlParameter(PARM_MESSAGETYPE, MySqlDbType.VarChar, 2),
                                                new MySqlParameter(PARM_FIRM, MySqlDbType.VarChar,3),
                                                new MySqlParameter(PARM_SIDE, MySqlDbType.VarChar,4),
                                                new MySqlParameter(PARM_ORDERNUMBER, MySqlDbType.VarChar,8),
                                                new MySqlParameter(PARM_ORDERENTRYDATE, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_FILTER, MySqlDbType.VarChar,12),
                                                new MySqlParameter(PARM_VOLUME, MySqlDbType.Int64),
                                                new MySqlParameter(PARM_PRICE, MySqlDbType.VarChar,12),
                                                new MySqlParameter(PARM_CONFIRMNUMBER, MySqlDbType.VarChar,20),
                                                new MySqlParameter(PARM_OFFSET, MySqlDbType.Int32)};
                parms[0].Value = timestamp;
                parms[1].Value = messagetype;
                parms[2].Value = firm;
                parms[3].Value = side;
                parms[4].Value = orderNumber;
                parms[5].Value = orderEntryDate;
                parms[6].Value = filter;
                parms[7].Value = volume;
                parms[8].Value = price;
                parms[9].Value = confirmNumber;
                parms[10].Value = offset;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, Insert_DealConfirmation, parms);
            }
            catch
            {
                throw;
            }
        }

        public void InsertPutThroughAcknowledgement(string timestamp, string messagetype, string firmBuyer, string tradeIdBuyer, string side, string ContraFirmSeller, string tradeIdSeller, string symbol, long volume, string price, string board, string confirmNumber, int offset)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                                new MySqlParameter(PARM_TIMESTAMP, MySqlDbType.VarChar, 6),
                                                new MySqlParameter(PARM_MESSAGETYPE, MySqlDbType.VarChar, 2),
                                                new MySqlParameter(PARM_FIRMBUYER, MySqlDbType.VarChar,3),
                                                new MySqlParameter(PARM_TRADEIDBUYER, MySqlDbType.VarChar, 4),
                                                new MySqlParameter(PARM_SIDE, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_CONTRAFIRMSELLER, MySqlDbType.VarChar,3),
                                                new MySqlParameter(PARM_TRADEIDSELLER, MySqlDbType.VarChar,4),
                                                new MySqlParameter(PARM_SYMBOL, MySqlDbType.VarChar,8),
                                                new MySqlParameter(PARM_VOLUME, MySqlDbType.Int64),
                                                new MySqlParameter(PARM_PRICE, MySqlDbType.VarChar,12),
                                                new MySqlParameter(PARM_BOARD, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_CONFIRMNUMBER, MySqlDbType.VarChar,6),
                                                new MySqlParameter(PARM_OFFSET, MySqlDbType.Int32)};
                parms[0].Value = timestamp;
                parms[1].Value = messagetype;
                parms[2].Value = firmBuyer;
                parms[3].Value = tradeIdBuyer;
                parms[4].Value = side;
                parms[5].Value = ContraFirmSeller;
                parms[6].Value = tradeIdSeller;
                parms[7].Value = symbol;
                parms[8].Value = volume;
                parms[9].Value = price;
                parms[10].Value = board;
                parms[11].Value = confirmNumber;
                parms[12].Value = offset;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, Insert_PutThroughAcknowledgement, parms);
            }
            catch
            {
                throw;
            }
        }

        public void InsertReject(string timestamp, string messagetype, string firm, string rejectReasonCode, string messageText, int offset)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                                new MySqlParameter(PARM_TIMESTAMP, MySqlDbType.VarChar, 6),
                                                new MySqlParameter(PARM_MESSAGETYPE, MySqlDbType.VarChar, 2),
                                                new MySqlParameter(PARM_FIRM, MySqlDbType.VarChar,3),
                                                new MySqlParameter(PARM_REJECTREASONCODE, MySqlDbType.VarChar,2),
                                                new MySqlParameter(PARM_MESSAGETEXT, MySqlDbType.VarChar,234),
                                                new MySqlParameter(PARM_OFFSET, MySqlDbType.Int32)};
                parms[0].Value = timestamp;
                parms[1].Value = messagetype;
                parms[2].Value = firm;
                parms[3].Value = rejectReasonCode;
                parms[4].Value = messageText;
                parms[5].Value = offset;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, Insert_Reject, parms);
            }
            catch
            {
                throw;
            }
        }

        public void InsertCrossDealConfirm(string timestamp, string messagetype, string firm, string orderNumberBuyer, string orderEntryDateBuyer, string orderNumberSeller, string orderEntryDateSeller, long volume, string price, string confirmNumber, int offset)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                                new MySqlParameter(PARM_TIMESTAMP, MySqlDbType.VarChar, 6),
                                                new MySqlParameter(PARM_MESSAGETYPE, MySqlDbType.VarChar, 2),
                                                new MySqlParameter(PARM_FIRM, MySqlDbType.VarChar,3),
                                                new MySqlParameter(PARM_ORDERNUMBERBUYER, MySqlDbType.VarChar,8),
                                                new MySqlParameter(PARM_ORDERENTRYDATEBUYER, MySqlDbType.VarChar,4),
                                                new MySqlParameter(PARM_ORDERNUMBERSELLER, MySqlDbType.VarChar,8),
                                                new MySqlParameter(PARM_ORDERENTRYDATESELLER, MySqlDbType.VarChar,4),
                                                new MySqlParameter(PARM_VOLUME, MySqlDbType.Int64),
                                                new MySqlParameter(PARM_PRICE, MySqlDbType.VarChar,6),
                                                new MySqlParameter(PARM_CONFIRMNUMBER, MySqlDbType.VarChar,6),
                                                new MySqlParameter(PARM_OFFSET, MySqlDbType.Int32)};
                parms[0].Value = timestamp;
                parms[1].Value = messagetype;
                parms[2].Value = firm;
                parms[3].Value = orderNumberBuyer;
                parms[4].Value = orderEntryDateBuyer;
                parms[5].Value = orderNumberSeller;
                parms[6].Value = orderEntryDateSeller;
                parms[7].Value = volume;
                parms[8].Value = price;
                parms[9].Value = confirmNumber;
                parms[10].Value = offset;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, Insert_CrossDealConfirm, parms);
            }
            catch
            {
                throw;
            }
        }

        public void InsertPutThroughDealConfirmation(string timestamp, string messagetype, string firm, string side, string dealId, string contraFirm, long volume, string price, string confirmNumver, int offset)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                                new MySqlParameter(PARM_TIMESTAMP, MySqlDbType.VarChar, 6),
                                                new MySqlParameter(PARM_MESSAGETYPE, MySqlDbType.VarChar, 2),
                                                new MySqlParameter(PARM_FIRM, MySqlDbType.VarChar,3),
                                                new MySqlParameter(PARM_SIDE, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_DEALID, MySqlDbType.VarChar,5),
                                                new MySqlParameter(PARM_CONTRAFIRM, MySqlDbType.VarChar,3),
                                                new MySqlParameter(PARM_VOLUME, MySqlDbType.Int64),
                                                new MySqlParameter(PARM_PRICE, MySqlDbType.VarChar,12),
                                                new MySqlParameter(PARM_CONFIRMNUMBER, MySqlDbType.VarChar,6),
                                                new MySqlParameter(PARM_OFFSET, MySqlDbType.Int32)};
                parms[0].Value = timestamp;
                parms[1].Value = messagetype;
                parms[2].Value = firm;
                parms[3].Value = side;
                parms[4].Value = dealId;
                parms[5].Value = contraFirm;
                parms[6].Value = volume;
                parms[7].Value = price;
                parms[8].Value = confirmNumver;
                parms[9].Value = offset;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, Insert_PutThroughDealConfirmation, parms);
            }
            catch
            {
                throw;
            }
        }

        public void InsertPutThroughDealReply(string timestamp, string messagetype, string firm, string confirmNumber, string dealId, string clientIdBuyer, string replyCode, string filter, long brokerPortfolioVolBuyer, long brokerClientVolBuyer, long mutualFundVolBuyer, long brokerForeignVolBuyer, string filter2, int offset)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                    new MySqlParameter(PARM_TIMESTAMP, MySqlDbType.VarChar, 6),
                                    new MySqlParameter(PARM_MESSAGETYPE, MySqlDbType.VarChar, 2),
                                    new MySqlParameter(PARM_FIRM, MySqlDbType.VarChar,3),
                                    new MySqlParameter(PARM_CONFIRMNUMBER, MySqlDbType.VarChar,6),
                                    new MySqlParameter(PARM_DEALID, MySqlDbType.VarChar,5),
                                    new MySqlParameter(PARM_CLIENTIDBUYER, MySqlDbType.VarChar,10),
                                    new MySqlParameter(PARM_REPLYCODE, MySqlDbType.VarChar,1),
                                    new MySqlParameter(PARM_FILTER, MySqlDbType.VarChar,4),
                                    new MySqlParameter(PARM_BROKERPORTFOLIOVOLBUYER, MySqlDbType.Int64),
                                    new MySqlParameter(PARM_BROKERCLIENTVOLBUYER, MySqlDbType.Int64),
                                    new MySqlParameter(PARM_MUTUALFUNDVOLBUYER, MySqlDbType.Int64),
                                    new MySqlParameter(PARM_BROKERFOREIGNVOLBUYER, MySqlDbType.Int64),
                                    new MySqlParameter(PARM_FILTER2, MySqlDbType.VarChar,32),
                                    new MySqlParameter(PARM_OFFSET, MySqlDbType.Int32)};
                parms[0].Value = timestamp;
                parms[1].Value = messagetype;
                parms[2].Value = firm;
                parms[3].Value = confirmNumber;
                parms[4].Value = dealId;
                parms[5].Value = clientIdBuyer;
                parms[6].Value = replyCode;
                parms[7].Value = filter;
                parms[8].Value = brokerPortfolioVolBuyer;
                parms[9].Value = brokerClientVolBuyer;
                parms[10].Value = mutualFundVolBuyer;
                parms[11].Value = brokerForeignVolBuyer;
                parms[12].Value = filter2;
                parms[13].Value = offset;

                //string temp = "INSERT INTO 3B_PutThroughDealReply (TimeStamp, MessageType, Firm, ConfirmNumber, DealID, ClientIDBuyer, ReplyCode, Filter, BrokerPortfolioVolBuyer, BrokerClientVolBuyer, MutualFundVolBuyer, BrokerForeignVolBuyer, Filter2, Offset) VALUES ('" + timestamp + "', '" + messagetype + "', '" + firm + "', '" + confirmNumber + "', '" + dealId + "','" + clientIdBuyer + "','" + replyCode + "','" + filter + "','" + brokerPortfolioVolBuyer + "','" + brokerClientVolBuyer + "','" + mutualFundVolBuyer + "','" + brokerForeignVolBuyer + "','" + filter2 + "','" + offset + "') ON DUPLICATE KEY UDPATE timestamp='" + timestamp + "', messagetype='" + messagetype + "', firm='" + firm + "', confirmNumber='" + confirmNumber + "', dealId='" + dealId + "',clientIdBuyer='" + clientIdBuyer + "',replyCode='" + replyCode + "',filter='" + filter + "',brokerPortfolioVolBuyer='" + brokerPortfolioVolBuyer + "',brokerClientVolBuyer='" + brokerClientVolBuyer + "',mutualFundVolBuyer='" + mutualFundVolBuyer + "',brokerForeignVolBuyer='" + brokerForeignVolBuyer + "',filter2='" + filter2 + "',offset='" + offset + "';";

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, Insert_PutThroughDealReply, parms);
            }
            catch
            {
                throw;
            }
        }

        public void InsertDealPutThroughCancelRequest(string timestamp, string messagetype, string firm, string contraFirm, string tradeId, string confirmNumber, string symbol, string side, int offset)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                                new MySqlParameter(PARM_TIMESTAMP, MySqlDbType.VarChar, 6),
                                                new MySqlParameter(PARM_MESSAGETYPE, MySqlDbType.VarChar, 2),
                                                new MySqlParameter(PARM_FIRM, MySqlDbType.VarChar,3),
                                                new MySqlParameter(PARM_CONTRAFIRM, MySqlDbType.VarChar,3),
                                                new MySqlParameter(PARM_TRADEID, MySqlDbType.VarChar,4),
                                                new MySqlParameter(PARM_CONFIRMNUMBER, MySqlDbType.VarChar,6),
                                                new MySqlParameter(PARM_SYMBOL, MySqlDbType.VarChar,8),
                                                new MySqlParameter(PARM_SIDE, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_OFFSET, MySqlDbType.Int32)};
                parms[0].Value = timestamp;
                parms[1].Value = messagetype;
                parms[2].Value = firm;
                parms[3].Value = contraFirm;
                parms[4].Value = tradeId;
                parms[5].Value = confirmNumber;
                parms[6].Value = symbol;
                parms[7].Value = side;
                parms[8].Value = offset;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, Insert_DealPutThroughCancelRequest, parms);
            }
            catch
            {
                throw;
            }
        }

        public void InsertDealCancelReply(string timestamp, string messagetype, string firm, string confirmNumber, string replyCode, int offset)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                                new MySqlParameter(PARM_TIMESTAMP, MySqlDbType.VarChar, 6),
                                                new MySqlParameter(PARM_MESSAGETYPE, MySqlDbType.VarChar, 2),
                                                new MySqlParameter(PARM_FIRM, MySqlDbType.VarChar,3),
                                                new MySqlParameter(PARM_CONFIRMNUMBER, MySqlDbType.VarChar,6),
                                                new MySqlParameter(PARM_REPLYCODE, MySqlDbType.VarChar,1),
                                                new MySqlParameter(PARM_OFFSET, MySqlDbType.Int32)};
                parms[0].Value = timestamp;
                parms[1].Value = messagetype;
                parms[2].Value = firm;
                parms[3].Value = confirmNumber;
                parms[4].Value = replyCode;
                parms[5].Value = offset;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, Insert_DealCancelReply, parms);
            }
            catch
            {
                throw;
            }
        }

        #endregion

        #region Count IOrder Members


        public int CountOrderCancellation()
        {
            try
            {
                return Convert.ToInt32(MyHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.Text, Count_OrderCancellation));
            }
            catch
            {
                throw;
            }
        }

        public int CountOrderChange()
        {
            try
            {
                return Convert.ToInt32(MyHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.Text, Count_OrderChange));
            }
            catch
            {
                throw;
            }
        }

        public int CountAdvertisement()
        {
            try
            {
                return Convert.ToInt32(MyHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.Text, Count_Advertisement));
            }
            catch
            {
                throw;
            }
        }

        public int CountOneFirmPutThroughDeal()
        {
            try
            {
                return Convert.ToInt32(MyHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.Text, Count_OneFirmPutThroughDeal));
            }
            catch
            {
                throw;
            }
        }

        public int CountTwoFirmPutThroughDeal()
        {
            try
            {
                return Convert.ToInt32(MyHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.Text, Count_TwoFirmPutThroughDeal));
            }
            catch
            {
                throw;
            }
        }

        public int CountNewConditionedOrder()
        {
            try
            {
                return Convert.ToInt32(MyHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.Text, Count_NewConditionedOrder));
            }
            catch
            {
                throw;
            }
        }

        public int CountOrderConfirmation()
        {
            try
            {
                return Convert.ToInt32(MyHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.Text, Count_OrderConfirmation));
            }
            catch
            {
                throw;
            }
        }

        public int CountConfirmOrderCancel()
        {
            try
            {
                return Convert.ToInt32(MyHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.Text, Count_ConfirmOrderCancel));
            }
            catch
            {
                throw;
            }
        }

        public int CountConfirmOrderChange()
        {
            try
            {
                return Convert.ToInt32(MyHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.Text, Count_ConfirmOrderChange));
            }
            catch
            {
                throw;
            }
        }

        public int CountDealConfirmation()
        {
            try
            {
                return Convert.ToInt32(MyHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.Text, Count_DealConfirmation));
            }
            catch
            {
                throw;
            }
        }

        public int CountPutThroughAcknowledgement()
        {
            try
            {
                return Convert.ToInt32(MyHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.Text, Count_PutThroughAcknowledgement));
            }
            catch
            {
                throw;
            }
        }

        public int CountReject()
        {
            try
            {
                return Convert.ToInt32(MyHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.Text, Count_Reject));
            }
            catch
            {
                throw;
            }
        }

        public int CountCrossDealConfirm()
        {
            try
            {
                return Convert.ToInt32(MyHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.Text, Count_CrossDealConfirm));
            }
            catch
            {
                throw;
            }
        }

        public int CountPutThroughDealConfirmation()
        {
            try
            {
                return Convert.ToInt32(MyHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.Text, Count_PutThroughDealConfirmation));
            }
            catch
            {
                throw;
            }
        }

        public int CountPutThroughDealReply()
        {
            try
            {
                return Convert.ToInt32(MyHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.Text, Count_PutThroughDealReply));
            }
            catch
            {
                throw;
            }
        }

        public int CountDealPutThroughCancelRequest()
        {
            try
            {
                return Convert.ToInt32(MyHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.Text, Count_DealPutThroughCancelRequest));
            }
            catch
            {
                throw;
            }
        }

        public int CountDealCancelReply()
        {
            try
            {
                return Convert.ToInt32(MyHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.Text, Count_DealCancelReply));
            }
            catch
            {
                throw;
            }
        }

        #endregion

        #region Delete IOrder Members


        public void DeleteOrderCancellation()
        {
            try
            {
                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, Delete_OrderCancellation);
            }
            catch
            {
                throw;
            }
        }

        public void DeleteOrderChange()
        {
            try
            {
                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, Delete_OrderChange);
            }
            catch
            {
                throw;
            }
        }

        public void DeleteAdvertisement()
        {
            try
            {
                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, Delete_Advertisement);
            }
            catch
            {
                throw;
            }
        }

        public void DeleteOneFirmPutThroughDeal()
        {
            try
            {
                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, Delete_OneFirmPutThroughDeal);
            }
            catch
            {
                throw;
            }
        }

        public void DeleteTwoFirmPutThroughDeal()
        {
            try
            {
                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, Delete_TwoFirmPutThroughDeal);
            }
            catch
            {
                throw;
            }
        }

        public void DeleteNewConditionedOrder()
        {
            try
            {
                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, Delete_NewConditionedOrder);
            }
            catch
            {
                throw;
            }
        }

        public void DeleteOrderConfirmation()
        {
            try
            {
                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, Delete_OrderConfirmation);
            }
            catch
            {
                throw;
            }
        }

        public void DeleteConfirmOrderCancel()
        {
            try
            {
                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, Delete_ConfirmOrderCancel);
            }
            catch
            {
                throw;
            }
        }

        public void DeleteConfirmOrderChange()
        {
            try
            {
                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, Delete_ConfirmOrderChange);
            }
            catch
            {
                throw;
            }
        }

        public void DeleteDealConfirmation()
        {
            try
            {
                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, Delete_DealConfirmation);
            }
            catch
            {
                throw;
            }
        }


        public void DeletePutThroughAcknowledgement()
        {
            try
            {
                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, Delete_PutThroughAcknowledgement);
            }
            catch
            {
                throw;
            }
        }

        public void DeleteReject()
        {
            try
            {
                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, Delete_Reject);
            }
            catch
            {
                throw;
            }
        }

        public void DeleteCrossDealConfirm()
        {
            try
            {
                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, Delete_CrossDealConfirm);
            }
            catch
            {
                throw;
            }
        }

        public void DeletePutThroughDealConfirmation()
        {
            try
            {
                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, Delete_PutThroughDealConfirmation);
            }
            catch
            {
                throw;
            }
        }

        public void DeletePutThroughDealReply()
        {
            try
            {
                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, Delete_PutThroughDealReply);
            }
            catch
            {
                throw;
            }
        }

        public void DeleteDealPutThroughCancelRequest()
        {
            try
            {
                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, Delete_DealPutThroughCancelRequest);
            }
            catch
            {
                throw;
            }
        }

        public void DeleteDealCancelReply()
        {
            try
            {
                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, Delete_DealCancelReply);
            }
            catch
            {
                throw;
            }
        }

        #endregion

        #region Select IHoseOrders Members


        public List<OrderNewData> getNewConditionedOrderData()
        {
            try
            {
                List<OrderNewData> list = new List<OrderNewData>();
                MySqlDataReader rdr = MyHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, Select_OrderNew);
                while (rdr.Read())
                {
                    list.Add(new OrderNewData(
                            rdr["timestamp"].ToString(),
                            rdr["ordernumber"].ToString(),
                            rdr["clientid"].ToString(),
                            rdr["securitysymbol"].ToString(),
                            rdr["side"].ToString(),
                            Convert.ToInt64(rdr["volume"]),
                            Convert.ToInt64(rdr["publishedvolume"]),
                            rdr["price"].ToString(),
                            rdr["board"].ToString(),
                            rdr["portclientflag"].ToString()));
                }
                rdr.Close();
                return list;
            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        public List<OrderChangeData> getOrderChangeData()
        {
            try
            {
                List<OrderChangeData> list = new List<OrderChangeData>();
                MySqlDataReader rdr = MyHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, Select_OrderChange);
                while (rdr.Read())
                {
                    list.Add(new OrderChangeData(
                            rdr["timestamp"].ToString(),
                            rdr["ordernumber"].ToString(),
                            rdr["clientid"].ToString()));
                }
                rdr.Close();
                return list;
            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        public List<OrderCancelData> getOrderCancelData()
        {
            try
            {
                List<OrderCancelData> list = new List<OrderCancelData>();
                MySqlDataReader rdr = MyHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, Select_OrderCancel);
                while (rdr.Read())
                {
                    list.Add(new OrderCancelData(
                            rdr["timestamp"].ToString(),
                            rdr["ordernumber"].ToString()));
                }
                rdr.Close();
                return list;
            }
            catch (System.Exception e)
            {
                throw e;
            }
        }


        public List<ConfirmOrderChangeData> getConfirmOrderChangeData()
        {
            try
            {
                List<ConfirmOrderChangeData> list = new List<ConfirmOrderChangeData>();
                MySqlDataReader rdr = MyHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, Select_ConfirmChange);
                while (rdr.Read())
                {
                    list.Add(new ConfirmOrderChangeData(
                            rdr["timestamp"].ToString(),
                            rdr["ordernumber"].ToString(),
                            rdr["clientid"].ToString(),
                            rdr["portclientflag"].ToString(),
                            Convert.ToInt64(rdr["PublishedVolume"]),
                            rdr["price"].ToString()));
                }
                rdr.Close();
                return list;
            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        public List<ConfirmCrossingDealData> getConfirmCrossingDealData()
        {
            try
            {
                List<ConfirmCrossingDealData> list = new List<ConfirmCrossingDealData>();
                MySqlDataReader rdr = MyHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, Select_ConfirmCross);
                while (rdr.Read())
                {
                    list.Add(new ConfirmCrossingDealData(
                            rdr["timestamp"].ToString(),
                            rdr["OrderNumberBuyer"].ToString(),
                            rdr["OrderNumberSeller"].ToString(),
                            Convert.ToInt64(rdr["Volume"]),
                            Convert.ToDecimal(rdr["price"]),
                            rdr["ConfirmNumber"].ToString()));
                }
                rdr.Close();
                return list;
            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        public List<ConfirmDealData> getConfirmDealData()
        {
            try
            {
                List<ConfirmDealData> list = new List<ConfirmDealData>();
                MySqlDataReader rdr = MyHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, Select_ConfirmDeal);
                while (rdr.Read())
                {
                    list.Add(new ConfirmDealData(
                            rdr["timestamp"].ToString(),
                            rdr["ordernumber"].ToString(),
                            rdr["side"].ToString(),
                            Convert.ToInt64(rdr["volume"]),
                            Convert.ToDecimal(rdr["price"]),
                            rdr["confirmnumber"].ToString(),
                            string.Empty));
                }
                rdr.Close();
                return list;
            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        public List<ConfirmOrderCancelData> getConfirmOrderCancelData()
        {
            try
            {
                List<ConfirmOrderCancelData> list = new List<ConfirmOrderCancelData>();
                MySqlDataReader rdr = MyHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, Select_ConfirmOrderCancel);
                while (rdr.Read())
                {
                    list.Add(new ConfirmOrderCancelData(
                            rdr["timestamp"].ToString(),
                            rdr["ordernumber"].ToString(),
                            Convert.ToInt64(rdr["CancelShares"]),
                            rdr["OrderCancelStatus"].ToString()));
                }
                rdr.Close();
                return list;
            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        public List<ConfirmOrderNewData> getConfirmOrderNewData()
        {
            try
            {
                List<ConfirmOrderNewData> list = new List<ConfirmOrderNewData>();
                MySqlDataReader rdr = MyHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, Select_ConfirmOrderNew);
                while (rdr.Read())
                {
                    list.Add(new ConfirmOrderNewData(
                            rdr["timestamp"].ToString(),
                            rdr["ordernumber"].ToString()));
                }
                rdr.Close();
                return list;
            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        #endregion

        #region Select IHosePtOrders Members


        public List<OFPTDealData> getOFPTDealData()
        {
            try
            {
                List<OFPTDealData> list = new List<OFPTDealData>();
                MySqlDataReader rdr = MyHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, Select_OFPTDealData);
                while (rdr.Read())
                {
                    list.Add(new OFPTDealData(
                            rdr["timestamp"].ToString(),
                            rdr["firm"].ToString(),
                            rdr["ClientIDBuyer"].ToString(),
                            rdr["ClientIDSeller"].ToString(),
                            rdr["SecuritySymbol"].ToString(),
                            rdr["Price"].ToString(),
                            rdr["DealID"].ToString()));
                }
                rdr.Close();
                return list;
            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        public List<TFPTDealData> getTFPTDealData()
        {
            try
            {
                List<TFPTDealData> list = new List<TFPTDealData>();
                MySqlDataReader rdr = MyHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, Select_TFPTDealData);
                while (rdr.Read())
                {
                    list.Add(new TFPTDealData(
                            rdr["timestamp"].ToString(),
                            rdr["FirmSeller"].ToString(),
                            rdr["ClientIDSeller"].ToString(),
                            rdr["ContraFirmBuyer"].ToString(),
                            rdr["SecuritySymbol"].ToString(),
                            rdr["Price"].ToString(),
                            rdr["DealID"].ToString()));
                }
                rdr.Close();
                return list;
            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        public List<ConfirmPtDealData> getConfirmPtDealData()
        {
            try
            {
                List<ConfirmPtDealData> list = new List<ConfirmPtDealData>();
                MySqlDataReader rdr = MyHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, Select_ConfirmPtDeal);
                while (rdr.Read())
                {
                    list.Add(new ConfirmPtDealData(
                            rdr["Timestamp"].ToString(),
                            rdr["Firm"].ToString(),
                            rdr["Side"].ToString(),
                            rdr["DealID"].ToString(),
                            rdr["ContraFirm"].ToString(),
                            rdr["Volume"].ToString(),
                            rdr["Price"].ToString(),
                            rdr["ConfirmNumber"].ToString()));
                }
                rdr.Close();
                return list;
            }
            catch (System.Exception e)
            {
                throw e;
            }
        }


        public List<OrderPtCancelRequestData> getOrderPtCancelRequest()
        {
            try
            {
                List<OrderPtCancelRequestData> list = new List<OrderPtCancelRequestData>();
                MySqlDataReader rdr = MyHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, Select_OrderPtCancelRequest);
                while (rdr.Read())
                {
                    list.Add(new OrderPtCancelRequestData(
                            rdr["Timestamp"].ToString(),
                            rdr["Firm"].ToString(),
                            rdr["ContraFirm"].ToString(),
                            rdr["TradeID"].ToString(),
                            rdr["ConfirmNumber"].ToString(),
                            rdr["SecuritySymbol"].ToString(),
                            rdr["Side"].ToString()));
                }
                rdr.Close();
                return list;
            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        public List<ConfirmPtDealCancelData> getConfirmPtDealCancelData()
        {
            try
            {
                List<ConfirmPtDealCancelData> list = new List<ConfirmPtDealCancelData>();
                MySqlDataReader rdr = MyHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, Select_ConfirmPtDealCancelData);
                while (rdr.Read())
                {
                    list.Add(new ConfirmPtDealCancelData(
                            rdr["Timestamp"].ToString(),
                            rdr["Firm"].ToString(),
                            rdr["ConfirmNumber"].ToString(),
                            rdr["ReplyCode"].ToString()));
                }
                rdr.Close();
                return list;
            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        public List<PtACKData> getPtACKData()
        {
            try
            {
                List<PtACKData> list = new List<PtACKData>();
                MySqlDataReader rdr = MyHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, Select_getPtACKData);
                while (rdr.Read())
                {
                    list.Add(new PtACKData(
                            rdr["timestamp"].ToString(),
                            rdr["FirmBuyer"].ToString(),
                            rdr["TradeIDBuyer"].ToString(),
                            rdr["Side"].ToString(),
                            rdr["ContraFirmSeller"].ToString(),
                            rdr["TradeIDSeller"].ToString(),
                            rdr["SecuritySymbol"].ToString(),
                            Convert.ToInt64(rdr["Volume"]),
                            rdr["Price"].ToString(),
                            rdr["Board"].ToString(),
                            rdr["confirmnumber"].ToString()));
                }
                rdr.Close();
                return list;
            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        public List<PtDealReplyData> getPtDealReplyData()
        {
            try
            {
                List<PtDealReplyData> list = new List<PtDealReplyData>();
                MySqlDataReader rdr = MyHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, Select_PtDealReplyData);
                while (rdr.Read())
                {
                    list.Add(new PtDealReplyData(
                            rdr["Timestamp"].ToString(),
                            rdr["Firm"].ToString(),
                            rdr["ConfirmNumber"].ToString(),
                            rdr["DealID"].ToString(),
                            rdr["ClientIDBuyer"].ToString(),
                            rdr["ReplyCode"].ToString(),
                            Convert.ToInt64(rdr["BrokerPortfolioVolBuyer"]),
                            Convert.ToInt64(rdr["BrokerClientVolBuyer"]),
                            Convert.ToInt64(rdr["MutualFundVolBuyer"]),
                            Convert.ToInt64(rdr["BrokerForeignVolBuyer"])));
                }
                rdr.Close();
                return list;
            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        #endregion

        public void BackupHoseOrders()
        {
            try
            {
                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, DeleteBackupOrders);
                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, InsertBackupHoseOrders);
            }
            catch
            {
                throw;
            }
        }

        public void DeleteHoseOrders()
        {
            try
            {
                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, Delete_Orders);
            }
            catch
            {
                throw;
            }
        }

        public int CountBackupOrders()
        {
            try
            {
                return Convert.ToInt32(MyHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.Text, Count_BackupOrders));
            }
            catch
            {
                return 0;
            }
        }

        public void UpdateSecuritiesInfo(int stockno, string symbol, string market)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                                new MySqlParameter("@STOCKNO", MySqlDbType.Int32),
                                                new MySqlParameter("@STOCKSYMBOL", MySqlDbType.VarChar),
                                                new MySqlParameter("@MARKET", MySqlDbType.VarChar)};
                parms[0].Value = stockno;
                parms[1].Value = symbol;
                parms[2].Value = market;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, Insert_SecuritiesInfo, parms);
            }
            catch
            {
                throw;
            }
        }

        #region IHoseOrders Select backup orders

        public List<OrderNewData> getBackupNewConditionedOrderData(DateTime from, DateTime to)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                                new MySqlParameter(PARM_FROMDATE, MySqlDbType.DateTime),
                                                new MySqlParameter(PARM_TODATE, MySqlDbType.DateTime)};
                parms[0].Value = from;
                parms[1].Value = to;

                List<OrderNewData> list = new List<OrderNewData>();
                MySqlDataReader rdr = MyHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, Select_Backup_OrderNew, parms);
                while (rdr.Read())
                {
                    list.Add(new OrderNewData(
                            Convert.ToDateTime(rdr["TransDate"]),
                            rdr["timestamp"].ToString(),
                            rdr["ordernumber"].ToString(),
                            rdr["clientid"].ToString(),
                            rdr["securitysymbol"].ToString(),
                            rdr["side"].ToString(),
                            Convert.ToInt64(rdr["volume"]),
                            Convert.ToInt64(rdr["publishedvolume"]),
                            rdr["price"].ToString(),
                            rdr["board"].ToString(),
                            rdr["portclientflag"].ToString()));
                }
                rdr.Close();
                return list;
            }
            catch
            {
                throw;
            }
        }

        public List<OrderChangeData> getBackupOrderChangeData(DateTime from, DateTime to)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                                new MySqlParameter(PARM_FROMDATE, MySqlDbType.DateTime),
                                                new MySqlParameter(PARM_TODATE, MySqlDbType.DateTime)};
                parms[0].Value = from;
                parms[1].Value = to;

                List<OrderChangeData> list = new List<OrderChangeData>();
                MySqlDataReader rdr = MyHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, Select_Backup_OrderChange, parms);
                while (rdr.Read())
                {
                    list.Add(new OrderChangeData(
                            Convert.ToDateTime(rdr["TransDate"]),
                            rdr["timestamp"].ToString(),
                            rdr["ordernumber"].ToString(),
                            rdr["clientid"].ToString()));
                }
                rdr.Close();
                return list;
            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        public List<OrderCancelData> getBackupOrderCancelData(DateTime from, DateTime to)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                                new MySqlParameter(PARM_FROMDATE, MySqlDbType.DateTime),
                                                new MySqlParameter(PARM_TODATE, MySqlDbType.DateTime)};
                parms[0].Value = from;
                parms[1].Value = to;

                List<OrderCancelData> list = new List<OrderCancelData>();
                MySqlDataReader rdr = MyHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, Select_Backup_OrderCancel, parms);
                while (rdr.Read())
                {
                    list.Add(new OrderCancelData(
                            Convert.ToDateTime(rdr["TransDate"]),
                            rdr["timestamp"].ToString(),
                            rdr["ordernumber"].ToString()));
                }
                rdr.Close();
                return list;
            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        public List<OFPTDealData> getBackupOFPTDealData(DateTime from, DateTime to)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                                new MySqlParameter(PARM_FROMDATE, MySqlDbType.DateTime),
                                                new MySqlParameter(PARM_TODATE, MySqlDbType.DateTime)};
                parms[0].Value = from;
                parms[1].Value = to;

                List<OFPTDealData> list = new List<OFPTDealData>();
                MySqlDataReader rdr = MyHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, Select_Backup_OFPTDealData, parms);
                while (rdr.Read())
                {
                    list.Add(new OFPTDealData(
                            Convert.ToDateTime(rdr["TransDate"]),
                            rdr["timestamp"].ToString(),
                            rdr["firm"].ToString(),
                            rdr["ClientIDBuyer"].ToString(),
                            rdr["ClientIDSeller"].ToString(),
                            rdr["SecuritySymbol"].ToString(),
                            rdr["Price"].ToString(),
                            rdr["DealID"].ToString()));
                }
                rdr.Close();
                return list;
            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        public List<TFPTDealData> getBackupTFPTDealData(DateTime from, DateTime to)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                                new MySqlParameter(PARM_FROMDATE, MySqlDbType.DateTime),
                                                new MySqlParameter(PARM_TODATE, MySqlDbType.DateTime)};
                parms[0].Value = from;
                parms[1].Value = to;

                List<TFPTDealData> list = new List<TFPTDealData>();
                MySqlDataReader rdr = MyHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, Select_Backup_TFPTDealData, parms);
                while (rdr.Read())
                {
                    list.Add(new TFPTDealData(
                            Convert.ToDateTime(rdr["TransDate"]),
                            rdr["timestamp"].ToString(),
                            rdr["FirmSeller"].ToString(),
                            rdr["ClientIDSeller"].ToString(),
                            rdr["ContraFirmBuyer"].ToString(),
                            rdr["SecuritySymbol"].ToString(),
                            rdr["Price"].ToString(),
                            rdr["DealID"].ToString()));
                }
                rdr.Close();
                return list;
            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        public List<OrderPtCancelRequestData> getBackupOrderPtCancelRequest(DateTime from, DateTime to)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                                new MySqlParameter(PARM_FROMDATE, MySqlDbType.DateTime),
                                                new MySqlParameter(PARM_TODATE, MySqlDbType.DateTime)};
                parms[0].Value = from;
                parms[1].Value = to;

                List<OrderPtCancelRequestData> list = new List<OrderPtCancelRequestData>();
                MySqlDataReader rdr = MyHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, Select_Backup_OrderPtCancelRequest, parms);
                while (rdr.Read())
                {
                    list.Add(new OrderPtCancelRequestData(
                            Convert.ToDateTime(rdr["TransDate"]),
                            rdr["Timestamp"].ToString(),
                            rdr["Firm"].ToString(),
                            rdr["ContraFirm"].ToString(),
                            rdr["TradeID"].ToString(),
                            rdr["ConfirmNumber"].ToString(),
                            rdr["SecuritySymbol"].ToString(),
                            rdr["Side"].ToString()));
                }
                rdr.Close();
                return list;
            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        public List<ConfirmOrderChangeData> getBackupConfirmOrderChangeData(DateTime from, DateTime to)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                                new MySqlParameter(PARM_FROMDATE, MySqlDbType.DateTime),
                                                new MySqlParameter(PARM_TODATE, MySqlDbType.DateTime)};
                parms[0].Value = from;
                parms[1].Value = to;

                List<ConfirmOrderChangeData> list = new List<ConfirmOrderChangeData>();
                MySqlDataReader rdr = MyHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, Select_Backup_ConfirmChange, parms);
                while (rdr.Read())
                {
                    list.Add(new ConfirmOrderChangeData(
                            Convert.ToDateTime(rdr["TransDate"]),
                            rdr["timestamp"].ToString(),
                            rdr["ordernumber"].ToString(),
                            rdr["clientid"].ToString(),
                            rdr["PortClientFlag"].ToString(),
                            Convert.ToInt64(rdr["PublishedVolume"]),
                            rdr["price"].ToString()));
                }
                rdr.Close();
                return list;
            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        public List<ConfirmCrossingDealData> getBackupConfirmCrossingDealData(DateTime from, DateTime to)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                                new MySqlParameter(PARM_FROMDATE, MySqlDbType.DateTime),
                                                new MySqlParameter(PARM_TODATE, MySqlDbType.DateTime)};
                parms[0].Value = from;
                parms[1].Value = to;

                List<ConfirmCrossingDealData> list = new List<ConfirmCrossingDealData>();
                MySqlDataReader rdr = MyHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, Select_Backup_ConfirmCross, parms);
                while (rdr.Read())
                {
                    list.Add(new ConfirmCrossingDealData(
                            Convert.ToDateTime(rdr["TransDate"]),
                            rdr["timestamp"].ToString(),
                            rdr["OrderNumberBuyer"].ToString(),
                            rdr["OrderNumberSeller"].ToString(),
                            Convert.ToInt64(rdr["Volume"]),
                            Convert.ToDecimal(rdr["price"]),
                            rdr["ConfirmNumber"].ToString()));
                }
                rdr.Close();
                return list;
            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        public List<ConfirmDealData> getBackupConfirmDealData(DateTime from, DateTime to)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                                new MySqlParameter(PARM_FROMDATE, MySqlDbType.DateTime),
                                                new MySqlParameter(PARM_TODATE, MySqlDbType.DateTime)};
                parms[0].Value = from;
                parms[1].Value = to;

                List<ConfirmDealData> list = new List<ConfirmDealData>();
                MySqlDataReader rdr = MyHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, Select_Backup_ConfirmDeal, parms);
                while (rdr.Read())
                {
                    list.Add(new ConfirmDealData(
                            Convert.ToDateTime(rdr["TransDate"]),
                            rdr["timestamp"].ToString(),
                            rdr["ordernumber"].ToString(),
                            rdr["side"].ToString(),
                            Convert.ToInt64(rdr["volume"]),
                            Convert.ToDecimal(rdr["price"]),
                            rdr["confirmnumber"].ToString(),
                            string.Empty));
                }
                rdr.Close();
                return list;
            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        public List<ConfirmOrderCancelData> getBackupConfirmOrderCancelData(DateTime from, DateTime to)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                                new MySqlParameter(PARM_FROMDATE, MySqlDbType.DateTime),
                                                new MySqlParameter(PARM_TODATE, MySqlDbType.DateTime)};
                parms[0].Value = from;
                parms[1].Value = to;

                List<ConfirmOrderCancelData> list = new List<ConfirmOrderCancelData>();
                MySqlDataReader rdr = MyHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, Select_Backup_ConfirmOrderCancel, parms);
                while (rdr.Read())
                {
                    list.Add(new ConfirmOrderCancelData(
                            Convert.ToDateTime(rdr["TransDate"]),
                            rdr["timestamp"].ToString(),
                            rdr["ordernumber"].ToString(),
                            Convert.ToInt64(rdr["CancelShares"]),
                            rdr["OrderCancelStatus"].ToString()));
                }
                rdr.Close();
                return list;
            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        public List<ConfirmOrderNewData> getBackupConfirmOrderNewData(DateTime from, DateTime to)
        {
            throw new NotImplementedException();
        }

        public List<ConfirmPtDealData> getBackupConfirmPtDealData(DateTime from, DateTime to)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                                new MySqlParameter(PARM_FROMDATE, MySqlDbType.DateTime),
                                                new MySqlParameter(PARM_TODATE, MySqlDbType.DateTime)};
                parms[0].Value = from;
                parms[1].Value = to;

                List<ConfirmPtDealData> list = new List<ConfirmPtDealData>();
                MySqlDataReader rdr = MyHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, Select_Backup_ConfirmPtDeal, parms);
                while (rdr.Read())
                {
                    list.Add(new ConfirmPtDealData(
                            Convert.ToDateTime(rdr["TransDate"]),
                            rdr["Timestamp"].ToString(),
                            rdr["Firm"].ToString(),
                            rdr["Side"].ToString(),
                            rdr["DealID"].ToString(),
                            rdr["ContraFirm"].ToString(),
                            rdr["Volume"].ToString(),
                            rdr["Price"].ToString(),
                            rdr["ConfirmNumber"].ToString()));
                }
                rdr.Close();
                return list;
            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        public List<ConfirmPtDealCancelData> getBackupConfirmPtDealCancelData(DateTime from, DateTime to)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                                new MySqlParameter(PARM_FROMDATE, MySqlDbType.DateTime),
                                                new MySqlParameter(PARM_TODATE, MySqlDbType.DateTime)};
                parms[0].Value = from;
                parms[1].Value = to;

                List<ConfirmPtDealCancelData> list = new List<ConfirmPtDealCancelData>();
                MySqlDataReader rdr = MyHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, Select_Backup_ConfirmPtDealCancelData, parms);
                while (rdr.Read())
                {
                    list.Add(new ConfirmPtDealCancelData(
                            Convert.ToDateTime(rdr["TransDate"]),
                            rdr["Timestamp"].ToString(),
                            rdr["Firm"].ToString(),
                            rdr["ConfirmNumber"].ToString(),
                            rdr["ReplyCode"].ToString()));
                }
                rdr.Close();
                return list;
            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        public List<PtACKData> getBackupPtACKData(DateTime from, DateTime to)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                                new MySqlParameter(PARM_FROMDATE, MySqlDbType.DateTime),
                                                new MySqlParameter(PARM_TODATE, MySqlDbType.DateTime)};
                parms[0].Value = from;
                parms[1].Value = to;

                List<PtACKData> list = new List<PtACKData>();
                MySqlDataReader rdr = MyHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, Select_Backup_getPtACKData, parms);
                while (rdr.Read())
                {
                    list.Add(new PtACKData(
                            Convert.ToDateTime(rdr["TransDate"]),
                            rdr["timestamp"].ToString(),
                            rdr["FirmBuyer"].ToString(),
                            rdr["TradeIDBuyer"].ToString(),
                            rdr["Side"].ToString(),
                            rdr["ContraFirmSeller"].ToString(),
                            rdr["TradeIDSeller"].ToString(),
                            rdr["SecuritySymbol"].ToString(),
                            Convert.ToInt64(rdr["Volume"]),
                            rdr["Price"].ToString(),
                            rdr["Board"].ToString(),
                            rdr["confirmnumber"].ToString()));
                }
                rdr.Close();
                return list;
            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        public List<PtDealReplyData> getBackupPtDealReplyData(DateTime from, DateTime to)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] { 
                                                new MySqlParameter(PARM_FROMDATE, MySqlDbType.DateTime),
                                                new MySqlParameter(PARM_TODATE, MySqlDbType.DateTime)};
                parms[0].Value = from;
                parms[1].Value = to;

                List<PtDealReplyData> list = new List<PtDealReplyData>();
                MySqlDataReader rdr = MyHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, Select_Backup_PtDealReplyData, parms);
                while (rdr.Read())
                {
                    list.Add(new PtDealReplyData(
                            Convert.ToDateTime(rdr["TransDate"]),
                            rdr["Timestamp"].ToString(),
                            rdr["Firm"].ToString(),
                            rdr["ConfirmNumber"].ToString(),
                            rdr["DealID"].ToString(),
                            rdr["ClientIDBuyer"].ToString(),
                            rdr["ReplyCode"].ToString(),
                            Convert.ToInt64(rdr["BrokerPortfolioVolBuyer"]),
                            Convert.ToInt64(rdr["BrokerClientVolBuyer"]),
                            Convert.ToInt64(rdr["MutualFundVolBuyer"]),
                            Convert.ToInt64(rdr["BrokerForeignVolBuyer"])));
                }
                rdr.Close();
                return list;
            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        public List<string> getVipAccount()
        {
            List<string> list = new List<string>();

            try
            {
                MySqlDataReader rdr = MyHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_VIPACCOUNT);

                while (rdr.Read())
                {
                    list.Add(rdr["ACCOUNTNO"].ToString());
                }
                rdr.Close();
                return list;
            }
            catch (System.Exception e)
            {
                throw e;
            }
        }
        #endregion
    }
}
